/* @(#)GSBalanceDAO.java   
 *
 * Project: GS
 *
 * Modify Information:
 * =============================================================================
 *   Author       Date       Description
 *   ------------ ---------- ---------------------------------------------------
 *   Jenny Li     2006-4-20     新增
 *   Jenny Li     2006-6-8      新增方法queryBalanceMsg3,getBalanceManageSql3
 *                              queryAllPayInfoByMonth,
 *   Simonyi Peng 2006-10-16    新增方法getBalanceMsg                         
 * Copyright Notice:
 * =============================================================================
 *    Copyright (c) 2001-2006 Beijing HiTRUST Technology Co., Ltd. 1808 Room, 
 *        Science & Technology Building, No. 9 South Street, Zhong Guan Cun, 
 *        Haidian District, Beijing ,100081, China All rights reserved.
 *
 *    This software is the confidential and proprietary information of
 *        Beijing HiTRUST Technology Co., Ltd. ("Confidential Information").
 *        You shall not disclose such Confidential Information and shall use it
 *        only in accordance with the terms of the license agreement you entered
 *        into with HiTRUST.
 *
 * Warning:
 * =============================================================================
 * 
 */
package com.hitrust.gs.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;

import com.hitrust.gs.bean.GSBalanceMsgBean;
import com.hitrust.gs.lib.GSBaseConstants;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.db.GSBaseDAO;
import com.hitrust.gs.util.date.DateUtil;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.vo.GSBalanceInfoVO;
import com.hitrust.gs.vo.GSBalanceManageQueryResultVO;
import com.hitrust.gs.vo.GSBalanceManageQueryVO;

/**
 * 
 */
public class GSBalanceMsgDAO extends GSBaseDAO {
    /**
     * 根据订货单位和结算月份查询已存在的结算信息
     * @param aOrderDeptId          订货单位编号
     * @param aYM                   结算月份
     * @return
     * @throws GSException
     */
    public ArrayList queryBalanceInfoByOrderDept(String aOrderDeptId,String aYM,String aSign)throws GSException{
        String tSql = "select * from GS_BALANCE_MSG where GS_WORKCORP_ID=? and GS_BALANCE_MONTH=? and GS_BALANCE_SIGN=?";
        Object[] tObj = {aOrderDeptId,aYM,aSign};
        ArrayList tList = new ArrayList();
        try{
            tList = this.executeQuery(new GSBalanceMsgBean(),tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
    }
    /**
     * 根据供应商和结算月份查询已存在的结算信息
     * @param aProviderId
     * @param aYM
     * @param aSign
     * @return
     * @throws GSException
     */
    public ArrayList queryBalanceInfoByOrderDeptForProv(String aProviderId,String aYM)throws GSException{
        String tSql = "select * from GS_BALANCE_MSG where GS_WORKCORP_ID=? and GS_BALANCE_MONTH=? ";
        Object[] tObj = {aProviderId,aYM};
        ArrayList tList = new ArrayList();
        try{
            tList = this.executeQuery(new GSBalanceMsgBean(),tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
    }
    public ArrayList queryBalanceInfo(String aOrderDeptId,String aYM)throws GSException{
        String tSql = "select * from GS_BALANCE_MSG where GS_WORKCORP_ID=? and GS_BALANCE_MONTH=?";
        Object[] tObj = {aOrderDeptId,aYM};
        ArrayList tList = new ArrayList();
        try{
            tList = this.executeQuery(new GSBalanceMsgBean(),tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
    }
    
    /**
     * 查询指定订货单位/供应商在指定结算月下的结算数据
     * @param aOrderDeptId
     * @param aYM
     * @return
     * @throws GSException
     */
    public ArrayList getBalanceMsg(String aOrderDeptId,String aYM)throws GSException{
        String tSql = "select * from GS_BALANCE_MSG where GS_WORKCORP_ID=? and GS_BALANCE_MONTH=? ";
        Object[] tObj = {aOrderDeptId,aYM};
        ArrayList tList = new ArrayList();
        try{
            tList = this.executeQuery(new GSBalanceMsgBean(),tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
    }
    
    /**
     * 新增结算信息到结算表
     * @param aVO               新增信息
     * @param aConn 
     * @return
     * @throws GSException
     */
    public boolean insertBalanceInfo(GSBalanceInfoVO aVO,Connection aConn) throws GSException{
        StringBuffer tSql = new StringBuffer("insert into GS_BALANCE_MSG (GS_WORKCORP_ID,GS_BALANCE_MONTH,");
            tSql.append("GS_WORKCORP_CATEGORY,GS_START_DATE,GS_END_DATE,GS_BALANCE_SUM,GS_ACTUAL_WORKCORP_ID,");
            tSql.append("GS_PAY_TYPE,GS_BALANCE_SIGN,GS_OPERATOR_ID,GS_OPERATE_DATE) values(");
            tSql.append("?,?,?,?,?,?,?,?,?,?,?)");
        boolean tFlag = false;
        Object[] tObj = new Object[11];
        tObj[0] = aVO.deptId;
        tObj[1] = aVO.balanceYM;
        tObj[2] = aVO.deptType;
        tObj[3] = aVO.startDate;
        tObj[4] = aVO.endDate;
        tObj[5] = new Double(aVO.totalAmt);
        tObj[6] = aVO.actualBalanceDept;
        tObj[7] = aVO.payType;
        tObj[8] = aVO.balanceFlag;
        tObj[9] = aVO.operatorId;
        tObj[10] = aVO.operateDate;
        try{
            tFlag = this.executeUpdate(aConn,tSql.toString(),tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
//            throw new GSException(GSException.E19995,GSException.E19995_MSG);
            throw new GSException(e.toString());
        }
        return tFlag;
    }
    /**
     * 新增结算信息到结算表
     * @param aVO               新增信息
     * @param aConn 
     * @return
     * @throws GSException
     */
    public boolean insertBalanceInfo2(GSBalanceInfoVO aVO,Connection aConn) throws GSException{
        StringBuffer tSql = new StringBuffer("insert into GS_BALANCE_MSG (GS_WORKCORP_ID,GS_BALANCE_MONTH,");
            tSql.append("GS_WORKCORP_CATEGORY,GS_START_DATE,GS_END_DATE,GS_BALANCE_SUM,GS_ACTUAL_WORKCORP_ID,");
            tSql.append("GS_PAY_TYPE,GS_BALANCE_SIGN,GS_OPERATOR_ID,GS_OPERATE_DATE,Gs_balance_msg) values(");
            tSql.append("?,?,?,?,?,?,?,?,?,?,?,?)");
        boolean tFlag = false;
        Object[] tObj = new Object[12];
        tObj[0] = aVO.deptId;
        tObj[1] = aVO.balanceYM;
        tObj[2] = aVO.deptType;
        tObj[3] = aVO.startDate;
        tObj[4] = aVO.endDate;
        tObj[5] = new Double(aVO.totalAmt);
        tObj[6] = aVO.actualBalanceDept;
        tObj[7] = aVO.payType;
        tObj[8] = aVO.balanceFlag;
        tObj[9] = aVO.operatorId;
        tObj[10] = aVO.operateDate;
        tObj[11]=aVO.balanceMSG;
        try{
            tFlag = this.executeUpdate(aConn,tSql.toString(),tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
//            throw new GSException(GSException.E19995,GSException.E19995_MSG);
            throw new GSException(e.toString());
        }
        return tFlag;
    }
    /**
     * 
     * @param aVO
     * @param aOrgList
     * @return
     * @throws GSException
     */
    public ArrayList queryBalanceMsg(GSBalanceManageQueryVO aVO,ArrayList aOrgList) throws GSException{
        ArrayList tList = new ArrayList();
        String tSql = this.getBalanceManageSql(aVO,aOrgList);
        try{
            tList = this.executeQuery(new GSBalanceManageQueryResultVO(),tSql,null);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(e.toString());
        }
        return tList;
    }
    /**
     * 
     * @param aVO
     * @param aOrgList
     * @return
     * @throws GSException
     */
    private String getBalanceManageSql(GSBalanceManageQueryVO aVO,ArrayList aOrgList) throws GSException{
        StringBuffer tSql = new StringBuffer();
        if(aVO.deptCategory.equals("0")){
            tSql.append(getOrderDeptBalanceMsg(aVO,aOrgList));
            tSql.append(" union ");
            tSql.append(getProviderBalanceMsg(aVO,aOrgList));
            tSql.append(" order by P9,P8,P1");
        }else if(aVO.deptCategory.equals(GSBaseConstants.BALANCE_ORDERDEPT_TYPE)){
            tSql.append(getOrderDeptBalanceMsg(aVO,aOrgList));
            tSql.append(" order by P9,P8,P1");
        }else if(aVO.deptCategory.equals(GSBaseConstants.BALANCE_PROVIDER_TYPE)){
            tSql.append(getProviderBalanceMsg(aVO,aOrgList));
            tSql.append(" order by P9,P8,P1");
        }
        GSLogWriter.info(this,tSql.toString());
        return tSql.toString();
    }
    /**
     * 
     * @param aVO
     * @param aOrgList
     * @return
     * @throws GSException
     */
    private String getOrderDeptBalanceMsg(GSBalanceManageQueryVO aVO,ArrayList aOrgList) throws GSException{
        StringBuffer tSql = new StringBuffer("select A.GS_WORKCORP_ID as P1, A.GS_BALANCE_MONTH as P2,");
        tSql.append("A.GS_WORKCORP_CATEGORY as P3,A.GS_BALANCE_SUM as P4,A.GS_PAY_TYPE as P5,");
        tSql.append("A.GS_BALANCE_SIGN as P6,B.GS_ORG_NAME as P7,B.GS_UPPERORG_ID as P8,A.GS_ACTUAL_WORKCORP_ID as P9,A.GS_BALANCE_MSG as P10");
        tSql.append(" from GS_BALANCE_MSG A,GS_ORGANIZATION B");
        tSql.append(" where A.GS_WORKCORP_ID=B.GS_ORG_ID");
        tSql.append(" and A.GS_BALANCE_MONTH = '" + aVO.balanceYM + "'");
        tSql.append(" and A.GS_BALANCE_SUM <> 0");
        
        if(!aVO.orderDeptId.equals("0000")){
            tSql.append(" and A.GS_WORKCORP_ID='" + aVO.orderDeptId + "'");
        }else{
            if(aOrgList.size() > 0){
                tSql.append(" and A.GS_WORKCORP_ID in(");
                for(int i=0; i<aOrgList.size();i++){
                    String tStr[] = (String[])aOrgList.get(i);
                        if( i == aOrgList.size()-1){
                            tSql.append("'"+tStr[0]+"')");
                        }else{
                            tSql.append("'"+tStr[0]+"',");
                        }
                }
            }
        }
        if(aVO.payType.equals("0")){
            tSql.append(" and A.GS_PAY_TYPE in('1','2')");
        }else{
            tSql.append(" and A.GS_PAY_TYPE ='" + aVO.payType + "'");
        }
        if(aVO.status.equals("00")){
            tSql.append(" and A.GS_BALANCE_SIGN in ('0','1')");
        }else{
            tSql.append(" and A.GS_BALANCE_SIGN ='" + aVO.status + "'");
        }
        return tSql.toString();
    }
    /**
     * 查询供应商结算信息SQL
     * @param aVO
     * @return
     * @throws GSException
     */
    private String getProviderBalanceMsg(GSBalanceManageQueryVO aVO,ArrayList aOrgList) throws GSException{
        StringBuffer tSql = new StringBuffer("select A.GS_WORKCORP_ID as P1, A.GS_BALANCE_MONTH as P2,");
        tSql.append("A.GS_WORKCORP_CATEGORY as P3,A.GS_BALANCE_SUM as P4,A.GS_PAY_TYPE as P5,");
        tSql.append("A.GS_BALANCE_SIGN as P6,B.GS_PROVIDER_SHORTNAME as P7,'' as P8,A.GS_ACTUAL_WORKCORP_ID as P9,A.GS_BALANCE_MSG as P10");
        tSql.append(" from GS_BALANCE_MSG A,GS_PROVIDER B");
        tSql.append(" where A.GS_WORKCORP_ID=B.GS_PROVIDER_ID");
        tSql.append(" and A.GS_BALANCE_MONTH = '" + aVO.balanceYM + "'");
/*        if(aVO.deptCategory.equals("0")){
            if(!aVO.orderDeptId.equals("0000")){
                tSql.append(" and A.GS_WORKCORP_ID='" + aVO.orderDeptId + "'");
            }
        }*///待定
        if(aVO.provider != null && !aVO.provider.equals("")){
            tSql.append(" and B.GS_PROVIDER_SHORTNAME='" + aVO.provider + "'");
        }
        if(aVO.payType.equals("0")){
            tSql.append(" and A.GS_PAY_TYPE in('1','2')");
        }else{
            tSql.append(" and A.GS_PAY_TYPE ='" + aVO.payType + "'");
        }
        if(aVO.status.equals("00")){
            tSql.append(" and A.GS_BALANCE_SIGN in ('0','1')");
        }else{
            tSql.append(" and A.GS_BALANCE_SIGN ='" + aVO.status + "'");
        }
        
        return tSql.toString();
    }
    /**
     * 删除结算信息
     * 
     * @param aDeptId
     * @param aMonth
     * @return
     * @throws GSException
     */
    public boolean abolishBalanceMsg (Connection aConn,String aDeptId,String aMonth) throws GSException{
        boolean tFlag = false;
        String tSql = "delete from GS_BALANCE_MSG where GS_WORKCORP_ID=? and GS_BALANCE_MONTH=?";
        Object[] tObj = new Object[2];
        tObj[0] = aDeptId;
        tObj[1] = aMonth;
        try{
            tFlag = this.executeUpdate(aConn,tSql,tObj);
        }catch(Exception e){
            e.printStackTrace();
            GSLogWriter.error(this,e.toString());
//            throw new GSException(GSException.E19995,GSException.E19995_MSG);
            throw new GSException(e.toString());
        }
        return tFlag;
    }
    /**
     * 修改结算金额
     * @param aConn
     * @param tProvider
     * @param aMonth
     * @return
     * @throws GSException
     */
    public boolean updateBalanceSum(Connection aConn,String[] tProvider,String aMonth,String aOperId) throws GSException{
        boolean tFlag = false;
        StringBuffer tSql = new StringBuffer("update GS_BALANCE_MSG set GS_BALANCE_SUM=GS_BALANCE_SUM+?, GS_OPERATOR_ID=?,GS_OPERATE_DATE=?");
        tSql.append("where GS_WORKCORP_ID=? and GS_BALANCE_MONTH=?");
        Object[] tObj = new Object[5];
        tObj[0] = tProvider[1];
        tObj[1] = aOperId;
        tObj[2] = DateUtil.getCurrentDate("yyyy-MM-dd hh:ss:mm");
        tObj[3] = tProvider[0];
        tObj[4] = aMonth;
        try{
            tFlag = this.executeUpdate(aConn,tSql.toString(),tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19995,GSException.E19995_MSG);
        }
        return tFlag;
    }
    public boolean updateBalanceSum(Connection aConn,String[] tProvider,String aMonth,String aOperId,String msg) throws GSException{
        boolean tFlag = false;
        StringBuffer tSql = new StringBuffer("update GS_BALANCE_MSG set GS_BALANCE_SUM=GS_BALANCE_SUM+?, GS_OPERATOR_ID=?,GS_OPERATE_DATE=?,gs_balance_msg=?");
        tSql.append("where GS_WORKCORP_ID=? and GS_BALANCE_MONTH=?");
        Object[] tObj = new Object[6];
        tObj[0] = tProvider[1];
        tObj[1] = aOperId;
        tObj[2] = DateUtil.getCurrentDate("yyyy-MM-dd hh:ss:mm");
        tObj[3]=msg;
        tObj[4] = tProvider[0];
        tObj[5] = aMonth;
        try{
            tFlag = this.executeUpdate(aConn,tSql.toString(),tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19995,GSException.E19995_MSG);
        }
        return tFlag;
    }
    /**
     * 修改结算状态
     * @param aConn
     * @param aStr
     * @return
     * @throws GSException
     */
    public boolean updateBalanceStatus(Connection aConn,String[] aStr) throws GSException{
    	boolean tFlag = false;
    	String tSql = "update GS_BALANCE_MSG set GS_BALANCE_SIGN=?,GS_OPERATOR_ID=?,GS_OPERATE_DATE=?," +
    			"GS_ACTUAL_WORKCORP_ID=? where GS_WORKCORP_ID=? and GS_BALANCE_MONTH=?";
    	Object[] tObj = new Object[6];
    	tObj[0] = aStr[0];
    	tObj[1] = aStr[1];
    	tObj[2] = DateUtil.getCurrentDate("yyyy-MM-dd hh:mm:ss");
    	tObj[3] = aStr[4];
    	tObj[4] = aStr[2];
    	tObj[5] = aStr[3];
    	try{
    		tFlag = this.executeUpdate(aConn,tSql,tObj);
    	}catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19995,GSException.E19995_MSG);    		
    	}
    	return tFlag;
    }
    /**
     * 修改结算金额
     * @param aConn
     * @param aBalMsgBean
     * @return
     * @throws GSException
     */
    public boolean updateBalanceSum(Connection aConn,GSBalanceMsgBean aBalMsgBean) throws GSException{
        boolean tFlag = false;
        StringBuffer tSql = new StringBuffer("update GS_BALANCE_MSG set GS_BALANCE_SUM=?,");
        tSql.append("GS_OPERATOR_ID=?,GS_OPERATE_DATE=?,GS_BALANCE_SIGN=? ");
        tSql.append("where GS_WORKCORP_ID=? and GS_BALANCE_MONTH=?");
        Object[] tObj = new Object[6];
        tObj[0] = String.valueOf(aBalMsgBean.getGs_balance_sum());
        tObj[1] = aBalMsgBean.getGs_operator_id();
        tObj[2] = aBalMsgBean.getGs_operate_date();
        tObj[3] = aBalMsgBean.getGs_balance_sign();
        tObj[4] = aBalMsgBean.getGs_workcorp_id();
        tObj[5] = aBalMsgBean.getGs_balance_month();
        try{
            tFlag = this.executeUpdate(aConn,tSql.toString(),tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19995,GSException.E19995_MSG);
        }
        return tFlag;
    }
    /**
     * 查询结算信息(根据2006-6-6需求变更)
     * @param aVO
     * @param aOrgList
     * @return
     * @throws GSException
     */
    public ArrayList queryBalanceMsg3(GSBalanceManageQueryVO aVO,ArrayList aOrgList) throws GSException{
        ArrayList tList = new ArrayList();
        String tSql = this.getBalanceManageSql3(aVO,aOrgList);
        try{
            tList = this.executeQuery(new GSBalanceManageQueryResultVO(),tSql,null);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(e.toString());
        }
        return tList;
    }
    /**
     * 查询供应商结算信息SQL
     * @param aVO
     * @return
     * @throws GSException
     */
    private String getBalanceManageSql4(GSBalanceManageQueryVO aVO) throws GSException{
        StringBuffer tSql = new StringBuffer("select A.GS_WORKCORP_ID as P1, A.GS_BALANCE_MONTH as P2,");
        tSql.append("A.GS_WORKCORP_CATEGORY as P3,A.GS_BALANCE_SUM as P4,A.GS_PAY_TYPE as P5,");
        tSql.append("A.GS_BALANCE_SIGN as P6,B.GS_PROVIDER_SHORTNAME as P7,'' as P8,A.GS_ACTUAL_WORKCORP_ID as P9,A.GS_BALANCE_MSG as P10");
        tSql.append(" from GS_BALANCE_MSG A,GS_PROVIDER B");
        tSql.append(" where A.GS_WORKCORP_ID=B.GS_PROVIDER_ID");
        tSql.append(" and A.GS_BALANCE_MONTH = '" + aVO.balanceYM + "'");
        if(aVO.provider != null && !aVO.provider.equals("")){
            tSql.append(" and (B.GS_PROVIDER_SHORTNAME like '%" + aVO.provider + "%' or GS_PROVIDER_NAME like '%"+aVO.provider+"%' ) ");
        }
        if(aVO.payType.equals("0")){
            tSql.append(" and A.GS_PAY_TYPE in('1','2')");
        }else{
            tSql.append(" and A.GS_PAY_TYPE ='" + aVO.payType + "'");
        }
        if(aVO.status.equals("00")){
            tSql.append(" and A.GS_BALANCE_SIGN in ('0','1')");
        }else{
            tSql.append(" and A.GS_BALANCE_SIGN ='" + aVO.status + "'");
        }
        return tSql.toString();
    }
    /**
     * 查询结算信息（供应商）
     * @param aVO
     * @
     * @return
     * @throws GSException
     */
    public ArrayList queryBalanceMsg4(GSBalanceManageQueryVO aVO) throws GSException{
        ArrayList tList = new ArrayList();
        String tSql = this.getBalanceManageSql4(aVO);
        try{
            tList = this.executeQuery(new GSBalanceManageQueryResultVO(),tSql,null);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(e.toString());
        }
        return tList;
    }
    /**
     * 查询待结算信息SQL
     * @param aVO
     * @param aOrgList
     * @return
     * @throws GSException
     */
    private String getBalanceManageSql3(GSBalanceManageQueryVO aVO,ArrayList aOrgList) throws GSException{
        StringBuffer tSql = new StringBuffer();
        tSql.append(getOrderDeptBalanceMsg(aVO,aOrgList));
        tSql.append(" order by P9,P8,P1");
        GSLogWriter.info(this,tSql.toString());
        return tSql.toString();
    }
    /**
     * 按月查询所有供应商的待付结算信息
     * @param aBalYM        月份
     * @return
     * @throws GSException
     */
    public ArrayList queryAllPayInfoByMonth(String aBalYM) throws GSException{
        ArrayList tList = new ArrayList();
        StringBuffer tSql = new StringBuffer("select * from GS_BALANCE_MSG where GS_BALANCE_MONTH='"+aBalYM +"' ");
        tSql.append("and GS_WORKCORP_CATEGORY='2' and GS_BALANCE_SIGN='0'");
        try{
            tList = this.executeQuery(new GSBalanceMsgBean(),tSql.toString(),null);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(e.toString());
        }
        return tList;
    }
    /**
     * 查询供应商对应的所有已完成的订单明细所对应订货单位的结算信息
     * @param aBalYM                结算月份
     * @param aProvider             供应商编号
     * @return
     * @throws GSException
     */
    public ArrayList queryOrdDeptUnBalMsgAssProvider(String aBalYM,String aProvider) throws GSException{
        ArrayList tList = new ArrayList();
        StringBuffer tSql = new StringBuffer("select A.* from GS_BALANCE_MSG A,GS_ORDER_DETAIL B,GS_ORDER C ");
        tSql.append(" where B.GS_BALANCE_MONTH_PROVIDER='"+aBalYM +"' and C.GS_ORDER_TEMPID=B.GS_ORDER_TEMPID");
        tSql.append(" and B.GS_PROVIDER_ID='"+aProvider+"' and C.GS_WORKCORP_ID=A.GS_WORKCORP_ID " );
        tSql.append(" and B.GS_ORDER_DETAIL_STATUS='14' and B.GS_IS_BALANCEED='1'");
        tSql.append(" and B.GS_BALANCE_MONTH=A.GS_BALANCE_MONTH order by A.GS_WORKCORP_CATEGORY");
        try{
            tList = this.executeQuery(new GSBalanceMsgBean(),tSql.toString(),null);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(e.toString());
        }
        return tList;
    }
    
    /**
     * 查询所有已执行结算审核的订货单位是否执行了应收
     * @param aBalYM                结算月份
     * @param aProvider             供应商编号
     * @return
     * @throws GSException
     */
    public ArrayList queryOrdDeptUnBalMsg(String aBalYM) throws GSException{
        ArrayList tList = new ArrayList();
        StringBuffer tSql = new StringBuffer("select A.* from GS_BALANCE_MSG A ");
        tSql.append(" where A.GS_BALANCE_MONTH='"+aBalYM +"' ");
        tSql.append(" and A.GS_WORKCORP_CATEGORY = '1' " );
        try{
            tList = this.executeQuery(new GSBalanceMsgBean(),tSql.toString(),null);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(e.toString());
        }
        return tList;
    }
    
    /**
     * 
     * 设置结算月应收结算金额为0的数据为已结算
     * @author Jackey
     * 
     * @param aConn
     * @param aBalYM                结算月份
     * 
     */
    public boolean setZeroMoneyBalanceSignYes(Connection aConn,String aBalYM) throws GSException
    {
    	boolean tFlag = false;
		String tSql = "update GS_BALANCE_MSG set GS_BALANCE_SIGN=? where GS_BALANCE_SUM = ? and GS_BALANCE_MONTH=? and GS_PAY_TYPE = ?";
		Object[] tObj = new Object[4];
		tObj[0] = "1";
		tObj[1] = "0";
		tObj[2] = aBalYM;
		tObj[3] = "1";
		try{
			tFlag = this.executeUpdate(aConn,tSql,tObj);
		}catch(Exception e){
			GSLogWriter.error(this,e.toString());
			throw new GSException(GSException.E19995,GSException.E19995_MSG);    		
		}
		return tFlag;    	
    }
    
    /**
     * 删除金额为0的记录
     * */
    public boolean deleteZero(Connection conn){
        boolean bl=false;
        String sql="delete from gs_balance_msg where GS_BALANCE_SUM <=0";
        try {
            bl=this.executeUpdate(conn,sql,null);
        } catch(Exception e){
            GSLogWriter.error(this,e.toString());
            //throw new GSException(GSException.E19995,GSException.E19995_MSG);           
        }
        return bl;
    }
}
